/*
 * DatabaseSchemaController.java
 *
 * Created on July 12, 2007, 10:47 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
package dnbW.uiW.dbW;

import dnbW.dbW.DataSource;
import dnbW.dbW.DatabaseInfo;
import dnbW.dbW.DatabaseList;
import dnbW.dbW.DatabaseEngine;
import dnbW.dbW.sucxentdbW.SucxentDatabaseEngine;

import dnbW.dbW.PathExpression;
import dnbW.dbW.PathExpression2;

import dnbW.utilW.Stopwatch;
import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;
import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Enumeration;
import java.util.List;
import java.util.Iterator;
import java.util.Set;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.DefaultTreeModel;
import javax.swing.tree.TreeSelectionModel;
import javax.swing.tree.TreePath;
import java.util.HashMap;

/**
 * A singleton class that controls all database related task
 * The core tasks are:
 * 1. add new database
 * 2. remove old database
 * 3. add new data source to a database
 * 4. remove data source
 * 5. load and remove documents from any data source
 * 6. get the query engine associated with this database
 *
 * Usage:
 *
 * This class controls all events related to the database panel
 * Database Panel only provides basic GUI components definitions and
 * resource injections like icon, text, tooltip, etc.
 *
 * Database Controller injects event handling code into the GUI class
 * when initilization. This class also maintains a reference to the
 * data model used by GUI components
 *
 * @author zhouyong
 */
public class DatabaseController {

    // <editor-fold desc="Singleton Pattern">
    private static DatabaseController INSTANCE = null;
    
    //static int count;
    
    public static HashMap<String, DefaultMutableTreeNode> dataSourceNodesList= new HashMap<String, DefaultMutableTreeNode>();
    
    public static DatabaseController getInstance() {
        if (INSTANCE == null) {
            INSTANCE = new DatabaseController();
            checkDrivers();
        }

        return INSTANCE;
    }

    /**
     * Creates a new instance of DatabaseSchemaController
     * This constructor is protected to ensure there is only
     * one instance created across the application
     */
    public DatabaseController() {
        // check whether SQL Server driver is available. If not, exit application.
//        try {
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//        } catch (ClassNotFoundException ex) {
//            ex.printStackTrace();
//            System.out.println("SQL Server driver missing...");
//            //DNB.getApplication().exit();
//        }
        
        

//        // initialize local fields
//        this.view = view;
//        // set schema tree view and actions
//        view.getSchemaTree().setTransferHandler(new PathContextTransferHandler());
//        view.getSchemaTree().setCellRenderer(new SchemaTreeCellRenderer());
//
//        view.getDatabaseListComboBox().setModel(this.databaseList);
//
//        // bind the currentDb property with the combobox
//        // the event is fired from other class
//        this.databaseList.addCurrentDatabasePropertyChangeListener(new CurrentDbPropertyChangeListener());
//
//        this.databaseList.addCurrentDatabasePropertyChangeListener(new CurrentDbPropertyChangeListenerForButton());
//
//        // set database combobox view and action
//        view.getDatabaseListComboBox().setRenderer(new DatabaseComboBoxRenderer());
    }
    
    public static void checkDrivers()
    {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
            System.out.println("SQL Server driver missing...");
            //DNB.getApplication().exit();
        }
    }
    
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Static Constants">
    public static final String SCHEMA_TREE_ROOT = "<root>";
    public static final String EMPTY_DATABASE_INFO = "Empty Database";
    public static final String DATA_SOURCE_PREFIX = "Data Source:";
    public static final String FAIL_INFO = "Schema Loading Failed";
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="View Property">
//    private DatabasePanel view;
//
//    public DatabasePanel getView() {
//        return view;
//    }
    // </editor-fold>

    // bound properties
    private DatabaseList databaseList = new DatabaseList();
    private IdentityPathNewQueryTranslatorForUpdate updateSQLTranslatorEngine = new IdentityPathNewQueryTranslatorForUpdate();

    // <editor-fold defaultstate="collapsed" desc="Unused">
    public void highlightPath(PathExpression v) {
//        view.getSchemaTree().clearSelection();
//
//        List<DefaultMutableTreeNode> path = v.getFullPath();
//
//        DefaultMutableTreeNode context =
//                (DefaultMutableTreeNode) view.getSchemaTree().getModel().getRoot();
//
//        for (DefaultMutableTreeNode node : path) {
//            for (Enumeration em = context.children(); em.hasMoreElements();) {
//                DefaultMutableTreeNode n = (DefaultMutableTreeNode) em.nextElement();
//
//                if (node.getUserObject().toString().equals(n.getUserObject().toString())) {
//                    context = n;
//                    view.getSchemaTree().addSelectionPath(new TreePath(context.getPath()));
//                    break;
//                }
//            }
//        }
    }
    // </editor-fold>

    /**
     * Retrieve the leaf values in the subtree whose root path is the path expression
     * @param pathExpression
     * @return
     */
    public Set<String> getLeafValues(PathExpression pathExpression) {
        SucxentDatabaseEngine engine =
                (SucxentDatabaseEngine) pathExpression.getContextDb().getEngine();
        return engine.getLeafValues(new PathExpression2(pathExpression));
    }

    public static void getPathId(
            PathExpression pathExpression,
            List<Integer> leafIdList,
            List<Integer> attributeIdList) {

        StringBuilder path = new StringBuilder();

        for (DefaultMutableTreeNode step : pathExpression.getFullPath()) {
            path.append(".");
            path.append(step.getUserObject().toString());
            path.append("#");
        }

        System.out.println("Path: " + path.toString());

        Connection dbcon = pathExpression.getContextDb().getDbConnection();

        try {
            Statement statement = dbcon.createStatement();
            String query =
                    "SELECT pathid, pathexp\n" +
                    "FROM " +
                    pathExpression.getDataSource().getName() + "_PATH\n" +
                    "WHERE PATHEXP LIKE '" + path.toString() + "%'";
            ResultSet results = statement.executeQuery(query);
            int id;
            String pathExp;
            while (results.next()) {
                pathExp = results.getString("pathexp");
                id = results.getInt("pathid");

                if (pathExp.contains("@")) {
                    if (attributeIdList != null) {
                        attributeIdList.add(id);
                    }
                } else {
                    if (leafIdList != null) {
                        leafIdList.add(id);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public String translateIdentityPathQueryForUpdate1(
            DatabaseInfo contextDb,
            String dataSrc,
            String tempTableName,
            String pathUName,
            String xpath,
            String oldOperator,
            String oldValue)
            throws Exception {

        if (!contextDb.isConnected()) {
            throw new Exception("Context Database: " + contextDb.getDbName() + " is not connected!");
        }

        if (!contextDb.getDataSourceNames().contains(dataSrc)) {
            throw new Exception(
                    "Context Database: " + contextDb.getDbName() +
                    " does not contain " + dataSrc);
        }

        BufferedWriter output = new BufferedWriter(new FileWriter("output.txt", true));
        output.write(xpath);
        output.write("\r\n");
        output.close();
        System.out.println("translating xpath: \n" + xpath);
        String sqlQuery =
                // this.identityPathTranslatorEngine.translate( -- replace by erwin 2008-10-07
                this.updateSQLTranslatorEngine.translateSQL1(
                contextDb.getDbConnection(),
                xpath,
                tempTableName,
                dataSrc,
                oldOperator,
                oldValue);
        sqlQuery = sqlQuery.replace("PathU", pathUName);
        System.out.println("translated sql query: \n" + sqlQuery);

        return sqlQuery;
    }
    
    public String translateIdentityPathQueryForUpdate2(
            DatabaseInfo contextDb,
            String dataSrc,
            String tempTableName,
            String pathUName,
            String xpath,
            String oldOperator,
            String oldValue)
            throws Exception {

        if (!contextDb.isConnected()) {
            throw new Exception("Context Database: " + contextDb.getDbName() + " is not connected!");
        }

        if (!contextDb.getDataSourceNames().contains(dataSrc)) {
            throw new Exception(
                    "Context Database: " + contextDb.getDbName() +
                    " does not contain " + dataSrc);
        }

        BufferedWriter output = new BufferedWriter(new FileWriter("output.txt", true));
        output.write(xpath);        
        output.write("\r\n");
        output.close();
        System.out.println("translating xpath: \n" + xpath);
        String sqlQuery =
                // this.identityPathTranslatorEngine.translate( -- replace by erwin 2008-10-07
                this.updateSQLTranslatorEngine.translateSQL2(
                contextDb.getDbConnection(),
                xpath,
                tempTableName,
                dataSrc,
                oldOperator,
                oldValue);
        sqlQuery = sqlQuery.replace("PathU", pathUName);
        System.out.println("translated sql query: \n" + sqlQuery);

        return sqlQuery;
    }

    public static int getIdentityPath(PathExpression outputPath, Connection con) throws Exception {

        //DatabaseInfo contextDb = contextQuery.getContextDb();
        //Connection dbconnection = con;
        Statement statement = con.createStatement();

        List<Integer> leafIdList = new ArrayList<Integer>();

        if (outputPath.isAttribute()) {
            List<DefaultMutableTreeNode> fullPath = outputPath.getFullPath();
            PathExpression parentPath =
                    new PathExpression(
                    fullPath.get(fullPath.size() - 2),
                    outputPath.getContextDb());
            parentPath.setDataSource(outputPath.getDataSource());
            getPathId(parentPath, leafIdList, null);
        } else {
            getPathId(outputPath, leafIdList, null);
        }

        System.out.println("Id List: " + leafIdList);


        StringBuilder pathIdList = new StringBuilder("(");
        for (int i = 0; i < leafIdList.size(); i++) {
            pathIdList.append(leafIdList.get(i));
            pathIdList.append(",");
        }
        pathIdList.deleteCharAt(pathIdList.lastIndexOf(","));
        pathIdList.append(")");

        //System.out.println(pathIdList);

        String tableName = "PathU" + controller.Controller.newQueryID;
        String deleteTableQuery =
                "if exists (select * from sys.tables where name like '" +
                tableName + "')\n" +
                "DROP TABLE " + tableName;
        statement.execute(deleteTableQuery);
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.start();
        String query =
                "select *\n" +
                "into " + tableName + "\n" +
                "from\n" +
                "(\n" +
                "SELECT V.DocId, V.AncestorDeweyGroup\n" +
                "FROM " + outputPath.getDataSource().getName() + "_PathValue V\n" +
                "WHERE V.PathID IN " + pathIdList + "\n";

        if (!outputPath.isLeaf() || !outputPath.isAttribute()) {
            query +=
                    "AND V.BranchOrder < " + outputPath.getFullPath().size() + "\n";
        }

        query += ") A\n" +
                "option (force order)\n";

        System.out.println("pathu query: \n" + query.toUpperCase());
        statement.execute(query);
        stopwatch.stop();
        System.out.println("THE RETRIEVAL TIME IS: " + stopwatch.readTime() + "ms");
        
        String pathufinal = "PATHUFINAL" + (controller.Controller.newQueryID);
        
        // delete the PATHU table if PATHFINAL table exists (?)
        deleteTableQuery = "if exists (select * from sys.tables where name like '" + pathufinal + "')\n " +
                "DROP TABLE " + pathufinal;
        statement.execute(deleteTableQuery);
        System.out.println(deleteTableQuery);

        // create a new one
        statement.executeUpdate("CREATE TABLE " + pathufinal + "(\n" +
                "DocId int, \n" +
                "AncestorDeweyGroup bigint\n)\n");

        ResultSet result = statement.executeQuery("select count(*) from " + tableName);
        result.next();

        int count1 = result.getInt(1);
        PrintWriter output = new PrintWriter(new BufferedWriter(new FileWriter("output.txt", true)));
        output.println("Retrieval Time = " + stopwatch.readTime() + "ms");
        output.println("Path Count: " + count1 + " identifiers");
        output.println();
        output.close();

        return count1;
    }

    public void deleteDatabase(DatabaseInfo db, boolean deepDelete) throws Exception {
        if (db == null) {
            db = this.getCurrentDb();
        }
        if (db == null) {
            return;
        }
        databaseList.removeElement(db);

        if (deepDelete) {
            db.getEngine().delete();
        }
    }
    
    public void deleteDatabase(DatabaseInfo db) throws Exception {
        if (db == null) {
            db = this.getCurrentDb();
        }
        if (db == null) {
            return;
        }
        databaseList.removeElement(db);
        
        //db.getEngine().delete();
        
        DiskStorageManager.saveDatabases(databaseList);

//        if (deepDelete) {
//            db.getEngine().delete();
//        }
    }

    public void addDatabase(DatabaseInfo db) throws Exception {
        if (db.getEngine() == null) {
            DatabaseEngine engine = new SucxentDatabaseEngine(db);
            db.setEngine(engine);
            engine.commitAdd();
            db.connect();
        }
        databaseList.addElement(db);
        this.setCurrentDb(db);
        
        DiskStorageManager.saveDatabases(databaseList);
    }

    /**
     * Load old databases' information list from file and connect to
     * each database to load all other informations
     */
    public void loadDatabaseList() {
        DiskStorageManager.loadDatabases(databaseList);
        LoadSchemaTask loadSchemaTask = new LoadSchemaTask();
        for (DatabaseInfo db : this.databaseList.getDatabases()) {
            try {
                    List<String> datasources = db.retrieveDataSources();
                    Iterator it = datasources.iterator();

                    while(it.hasNext())
                    {
                        String datasource = (String) it.next();
                        DataSource dataSource = db.getDataSource(datasource);
                        DefaultMutableTreeNode dataSourceNode = loadSchemaTask.loadSchema(db, dataSource);
                        dataSourceNodesList.put(datasource, dataSourceNode);
                    }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * Save the database list to a file when application terminates
     */
//    public void saveDatabaseList() {
//        DiskStorageManager.saveDatabases(databaseList);
//    }

//    @Action
//    public Task loadSchema() {
//        Object item = this.databaseList.getSelectedItem();
//
//        DatabaseInfo currentDb;
//        if (item instanceof String) {
//            currentDb = null;
//        } else {
//            currentDb = (DatabaseInfo) item;
//        }
//
//        //Task task = new LoadSchemaTask(DNB.getApplication(), currentDb, null);
//        return null;
//    }

    public void addDocument(DatabaseInfo db, File file, String dataSourceName) throws Exception {
        //String fileNm = "D:\\Company3.xml";
        db.getEngine().loadDocumentFromFile(file.getAbsolutePath(), dataSourceName);// SucxentDatabaseEngine.java line 209
//        Task loadSchemaTask = new LoadSchemaTask(db, Collections.singletonList(db.getDataSource(dataSourceName)));
//        loadSchemaTask.execute();
    }

    public Collection<DatabaseInfo> getDatabaseList() {
        return this.databaseList.getDatabases();
    }

    public DatabaseInfo getCurrentDb() {
        return (DatabaseInfo)this.databaseList.getSelectedItem();
    }

    public void setCurrentDb(DatabaseInfo currentDb) {       
        this.databaseList.setSelectedItem(currentDb);
    }

    public static boolean isDataSourceNode(DefaultMutableTreeNode node) {
        return node.getUserObject() instanceof DataSourceNodeObject;
    }

    public static String getDataSourceName(DefaultMutableTreeNode node) {
        if (!(node.getUserObject() instanceof DataSourceNodeObject)) return "";
        else {
            DataSourceNodeObject dataSource = (DataSourceNodeObject)node.getUserObject();
            return dataSource.getSrcName();
        }
    }

    // <editor-fold desc="Inner-class Listeners">
    // "currentDb" property change listener
    // this listener can be invoked either by
    // 1. User GUI action, i.e. user selects a different item from the
    // database combobox
    // 2. programmingly by setting "currentDb" property
    private class CurrentDbPropertyChangeListener implements PropertyChangeListener {

        @Override
        public void propertyChange(PropertyChangeEvent evt) {
//            DatabaseInfo db = (DatabaseInfo) evt.getNewValue();
//
//            if (db == null || db.isDefault()) {
//                DefaultMutableTreeNode root = new DefaultMutableTreeNode(SCHEMA_TREE_ROOT);
//                root.add(new DefaultMutableTreeNode(DatabaseInfo.DEFAULT_DATABASE_NAME));
//                DefaultTreeModel treeModel = new DefaultTreeModel(root);
//                getView().getSchemaTree().setModel(treeModel);
//                getView().getDatabaseListComboBox().setSelectedIndex(0);
//            } else {
//                //databaseListComboBoxModel.setSelectedItem(db);
//                getView().getDatabaseListComboBox().setSelectedItem(db);
//                if (!db.isSchemaAvailable() && !db.getDataSourceNames().isEmpty()) {
//                    // set up a temp tree model
//                    DefaultMutableTreeNode root =
//                            new DefaultMutableTreeNode(SCHEMA_TREE_ROOT);
//                    root.add(new DefaultMutableTreeNode("Refreshing Schema..."));
//                    getView().getSchemaTree().setModel(new DefaultTreeModel(root));
//
//                    // load the schema in background task
////                    Task loadSchemaTask = new LoadSchemaTask(db, db.getDataSources());
////                    loadSchemaTask.execute();
//                }
//
//                getView().getSchemaTree().setModel(db.getTreeModel());
//                getView().getSchemaTree().getSelectionModel().setSelectionMode(TreeSelectionModel.SINGLE_TREE_SELECTION);
//                db.setSchemaAvailable(true);
//            }
        }
    }

    private class CurrentDbPropertyChangeListenerForButton implements PropertyChangeListener {

        @Override
        public void propertyChange(PropertyChangeEvent evt) {
            DatabaseInfo db = (DatabaseInfo) evt.getNewValue();
            if (db == null) {
//                ((DNBView) DNB.getApplication().getMainView()).hideDataSourceButtons();
//                ((DNBView) DNB.getApplication().getMainView()).hideLoadDocButton();
            } else {
//                DNBView dnbView = (DNBView) DNB.getApplication().getMainView();
//                dnbView.showDataSourceButtons();
//                dnbView.showLoadDocButton();
            }
        }
    }
    // </editor-fold>
}
